﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DSheet.Core.Data;
using System.Data.SqlClient;
using System.Data;

namespace DSheet.Core
{
    public class DimDataService
    {
        public List<DimData> GetAllDims(DBContext context)
        {
            List<DimData> datas = new List<DimData>();

            SqlCommand command = context.DBConnection.CreateCommand();
            string sql = "select * from dimdata";
            command.CommandText = sql;

            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                DimData data = new DimData();
                data.Id = new Guid(Convert.ToString(reader["id"]));
                data.Code = Convert.ToString(reader["code"]);
                data.Name = Convert.ToString(reader["name"]);
                datas.Add(data);
            }
            reader.Close();

            return datas;
        }

        public DimData GetDimData(DBContext context, Guid id)
        {
            DimData data = null;

            SqlCommand command = context.DBConnection.CreateCommand();
            string sql = "select * from dimdata where id=@id";
            command.CommandText = sql;
            command.Parameters.Add(new SqlParameter("@id", id));

            SqlDataReader reader = command.ExecuteReader();
            if (reader.Read())
            {
                data = new DimData();
                data.Id = new Guid(Convert.ToString(reader["id"]));
                data.Code = Convert.ToString(reader["code"]);
                data.Name = Convert.ToString(reader["name"]);
            }
            reader.Close();

            if (data != null)
            {
                SqlCommand queryItemCommand = context.DBConnection.CreateCommand();
                string queryItemSql = "select * from dimitem where dataid=@dataid";
                queryItemCommand.CommandText = queryItemSql;
                queryItemCommand.Parameters.Add(new SqlParameter("@dataid", id));
                DataSet itemSet = new DataSet();
                SqlDataAdapter queryItemAdapter = new SqlDataAdapter(queryItemCommand);
                queryItemAdapter.Fill(itemSet);

                Stack<DimItem> stack = new Stack<DimItem>();
                DataRow[] rootRows = itemSet.Tables[0].Select(string.Format("parentid is null or parentid='{0}'", Guid.Empty));
                foreach (var rootRow in rootRows)
                {
                    DimItem item = new DimItem();
                    item.Id = new Guid(Convert.ToString(rootRow["id"]));
                    item.Code = Convert.ToString(rootRow["code"]);
                    item.Name = Convert.ToString(rootRow["name"]);
                    item.ParentId = new Guid(Convert.ToString(rootRow["parentid"]));
                    item.DataId = new Guid(Convert.ToString(rootRow["dataid"]));
                    data.Items.Add(item);
                    stack.Push(item);
                }

                while (stack.Count > 0)
                {
                    DimItem pitem = stack.Pop();
                    DataRow[] itemRows = itemSet.Tables[0].Select(string.Format("parentid='{0}'", pitem.Id));
                    foreach (var itemRow in itemRows)
                    {
                        DimItem item = new DimItem();
                        item.Id = new Guid(Convert.ToString(itemRow["id"]));
                        item.Code = Convert.ToString(itemRow["code"]);
                        item.Name = Convert.ToString(itemRow["name"]);
                        item.ParentId = new Guid(Convert.ToString(itemRow["parentid"]));
                        item.DataId = new Guid(Convert.ToString(itemRow["dataid"]));
                        pitem.AddItem(item);
                        stack.Push(item);
                    }
                }
            }

            return data;
        }

        public bool SaveDimData(DBContext context, DimData data)
        {
            SqlCommand command = context.DBConnection.CreateCommand();
            command.CommandText = "insert into dimdata (id,code,name) values(@id,@code,@name)";
            command.Parameters.Add(new SqlParameter("@id", data.Id));
            command.Parameters.Add(new SqlParameter("@code", data.Code));
            command.Parameters.Add(new SqlParameter("@name", data.Name));
            if (command.ExecuteNonQuery() > 0)
            {
                return true;
            }
            return false;
        }

        public bool SaveDimItem(DBContext context, Guid dataId, Guid parentId, DimItem item)
        {
            SqlCommand command = context.DBConnection.CreateCommand();
            command.CommandText = "insert into dimitem (id,code,name,parentid,dataid) values(@id,@code,@name,@parentid,@dataid)";
            command.Parameters.Add(new SqlParameter("@id", item.Id));
            command.Parameters.Add(new SqlParameter("@code", item.Code));
            command.Parameters.Add(new SqlParameter("@name", item.Name));
            command.Parameters.Add(new SqlParameter("@dataid", dataId));
            command.Parameters.Add(new SqlParameter("@parentid", parentId));
            if (command.ExecuteNonQuery() > 0)
            {
                return true;
            }
            return false;
        }

        public bool DeleteDimData(DBContext context, Guid dataId)
        {
            SqlCommand command = context.DBConnection.CreateCommand();
            command.CommandText = "delete from dimdata where id=@id";
            command.Parameters.Add(new SqlParameter("@id", dataId));
            command.ExecuteNonQuery();

            command = context.DBConnection.CreateCommand();
            command.CommandText = "delete from dimitem where dataid=@dataid";
            command.Parameters.Add(new SqlParameter("@dataid", dataId));
            command.ExecuteNonQuery();

            return false;
        }

        public DimItem GetDimItem(DBContext context, Guid dataId, string itemCode)
        {
            DimItem item = null;
            
            SqlCommand queryItemCommand = context.DBConnection.CreateCommand();
            string queryItemSql = "select * from dimitem where dataid=@dataid and code=@code";
            queryItemCommand.CommandText = queryItemSql;
            queryItemCommand.Parameters.Add(new SqlParameter("@dataid", dataId));
            queryItemCommand.Parameters.Add(new SqlParameter("@code", itemCode));

            SqlDataReader reader = queryItemCommand.ExecuteReader();
            if (reader.Read())
            {
                item = new DimItem();
                item.Id = new Guid(Convert.ToString(reader["id"]));
                item.Code = Convert.ToString(reader["code"]);
                item.Name = Convert.ToString(reader["name"]);
                item.ParentId = new Guid(Convert.ToString(reader["parentid"]));
                item.DataId = new Guid(Convert.ToString(reader["dataid"]));
            }
            reader.Close();

            return item;
        }
    }
}
